from PyQt4.QtCore import *
from PyQt4.QtGui import *
from libraries.base.signalClasses.RDataFrame import *
from libraries.base.signalClasses.TableView import *
from libraries.base.signalClasses.RMatrix import *
import time, sqlite3, os
import redRGUI, redREnviron, redR

from libraries.base.qtWidgets.widgetBox import widgetBox
from libraries.base.qtWidgets.groupBox import groupBox
from libraries.base.qtWidgets.button import button
from libraries.base.qtWidgets.widgetLabel import widgetLabel
from libraries.base.qtWidgets.scrollArea import scrollArea
from libraries.base.qtWidgets.lineEdit import lineEdit
from libraries.base.qtWidgets.checkBox import checkBox
import redRi18n
_ = redRi18n.get_(package = 'base')

class SQLiteTable(RDataFrame, StructuredDict):
    convertToList = [RDataFrame, RList, RVariable, TableView, RMatrix]
    def __init__(self, widget, data, database, parent = None, checkVal = True, **kwargs):
        RDataFrame.__init__(self, widget = widget, data = data, parent = parent, checkVal = False)
        StructuredDict.__init__(self, widget = widget, data = data, parent = parent, checkVal = False)
        TableView.__init__(self)
        ##  Need to set some specific parameters for SQLiteTable objects.  Such as the database and other stuff
        if parent:  # sets the parent (ideally a real table in the database) of the data that you have here.  This may not be used that much, especially if you merge things, but you have it anyway if you need it.
            self.parent = parent
        else:
            self.parent = data
        self.workingDatabase = database
        self.newDataName = 'dataFrameConversion_'+unicode(time.time()) # put this here because we may make many connections from this output and we only want one dataFrameConversion_ in R
        
        c = self.getCursor()
        print 'Got cursor'
        c.execute('SELECT * FROM (%s) LIMIT 1' % (self.getData(),))
        print 'Selected elements'
        self._colnames = [n[0] for n in c.description]
        print 'Setting columns'
        self._ncol = len(c.fetchall()[0])
        print 'Selecting Counts'
        self._nrow = c.execute('SELECT "%s" from (%s)' % (self._colnames[0], self.getData())).rowcount
        self.closeCursor(c)
        print 'Cursor closed'
        self.workingDataFrameData = None
        self.StructuredDictSignal = None
        self.workingMatrix = None
    def colnames(self):
        return self._colnames
    def ncol(self):
        return self._ncol
    def nrow(self):
        return self._nrow
    def saveSettings(self):
        r = RDataFrame.saveSettings(self)
        r['database'] = self.workingDatabase
        r['newDataName'] = self.newDataName
        return r
    def loadSettings(self, settings):
        RDataFrame.loadSettings(self, settings)
        self.data = settings['data']
        self.workingDatabase = settings['database']
        self.newDataName = settings['newDataName']
    def convertToClass(self, varClass):
        if varClass == RList:
            return self._convertToList()
        elif varClass == SQLiteTable:
            return self
        elif varClass == BaseRedRVariable:
            return self
        elif varClass == RVariable:
            return self._convertToDataFrame()
        elif varClass == RDataFrame:
            return self._convertToDataFrame()
        
        elif varClass == StructuredDict:
            return self._convertToStructuredDict()
        elif varClass == UnstructuredDict:
            return self._convertToStructuredDict()
        elif varClass == TableView:
            return self
        elif varClass == RMatrix:
            return self._convertToMatrix()
        else:
            raise Exception
    def getDatabase(self):
        if 'local|' in self.workingDatabase:  # convert the database if the local name is present.
            database = os.path.join(redREnviron.directoryNames['tempDir'], self.workingDatabase.split('|')[1])
        else:
            database = self.workingDatabase
        return database
    def _convertToMatrix(self):
        if self.workingMatrix:
            return self.workingMatrix
        database = self.getDatabase()
        if not os.path.exists(database):
            if self.R('exists('+self.newDataName+')') == 'TRUE':
                QMessageBox.information(self, 'SQLite Conversion','Database '+unicode(database)+' does not exist on your system.\nHowever, I found the data in the Red-R session and will send this\ninstead of making a new table.',  QMessageBox.Ok + QMessageBox.Default)
                newData = RDataFrame(self.widget, data = self.newDataName)
                return newData
            else:
                QMessageBox.information(self, 'SQLite Conversion','Database '+unicode(database)+' does not exist on your system.\nIf you got this schema from someone else this is normal.\nI\'ll make the connection but no data will be sent.',  QMessageBox.Ok + QMessageBox.Default)
                return
        database = database.replace('\\','/')
        if not self.require_librarys(['RSQLite']): # require the sqlite package
            QMessageBox.information(self, 'SQLite Conversion','Can\'t load the RSQLite package.  Please connect to the internet do download.',  QMessageBox.Ok + QMessageBox.Default)
            return
            
            
        if not self.R('exists("%s")' % self.newDataName):
            conn = sqlite3.connect(database)
            cursor = conn.cursor()
            
            cursor.execute('PRAGMA table_info('+self.getData()+')')
            types = []
            for row in cursor:
                types.append((row[1], row[2]))
            conn.close()
            
            ### list all of the column names and allow the user to specify one as the rowname column if desired.
            colnames = [type[0] for type in types]
            colnames.insert(0, 'No Names')
            dialog = redRGUI.base.dialog()
            rownameList = redRGUI.base.listBox(dialog, label = 'Rownames', toolTip = 'Select a column to represent Row Names', callback = dialog.accept)
            rownameList.update(colnames)
            r = dialog.exec_() ## execute the dialog
            if r == QDialog.Accepted and unicode(rownameList.selectedIds()[0]) != 'No Names':
                
                rownameSelection = '\''+unicode(rownameList.selectedIds()[0])+'\''
            else:
                rownameSelection = 'NULL'
            self.R('m<-dbDriver("SQLite")')
            print database
            self.R('con<-dbConnect(m, dbname=\''+database+'\')')
            
            
            self.R(self.newDataName+'<-dbGetQuery(con, statement=\'select * from '+self.getData()+'\', row.names = '+rownameSelection+')')
            self.R('dbDisconnect(con)')  # close the connection
            # it would be really nice if in this we could ask the user to pick the column for the column name
            
            
            # convert the classes of the columns to something reasonable like numeric or factor
            
            for type in types:
                if type[1] in ['text']:
                    self.R(self.newDataName+'$'+unicode(type[0])+'<-as.factor('+self.newDataName+'$'+unicode(type[0])+')')
                elif type[1] in ['real']:
                    self.R(self.newDataName+'$'+unicode(type[0])+'<-as.numeric('+self.newDataName+'$'+unicode(type[0])+')')
            
            if rownameSelection != 'NULL':
                self.R('rownames('+self.newDataName+')<-'+self.newDataName+'$'+rownameSelection.strip('\''))
                self.R(self.newDataName+'<-'+self.newDataName+'[,colnames('+self.newDataName+') != '+rownameSelection+',drop = F]')
            ## allow the user to set rownames???
        
        
        self.workingMatrix = RMatrix(self.parent, data = 'data.matrix(%s)' % self.newDataName)
        return self.workingMatrix
    def _convertToStructuredDict(self):
        if not self.StructuredDictSignal:
            ## convert to a python object that is a structured dict.
            if 'local|' in self.workingDatabase:  # convert the database if the local name is present.
                database = os.path.join(qApp.canvasDlg.tempDir, self.workingDatabase.split('|')[1])
            else:
                database = self.workingDatabase
            conn = sqlite3.connect(database)
            cursor = conn.cursor()
            cursor.execute('PRAGMA table_info('+self.workingData+')')
            dictData = {}
            colnames = []
            for row in cursor:
                dictData[unicode(row[1])] = []  # make an empty list in the beginning.
                colnames.append(unicode(row[1]))
            
            cursor.execute('select * from '+self.workingData)
            for row in cursor: ## returns a structured tuple of values that will go into the dict.  But these values are based on rows instead of columns as the dict will be
                for i in range(len(colnames)):
                    dictData[colnames[i]].append(row[i])
                    
            conn.close()
            self.StructuredDictSignal = StructuredDict(data = dictData, parent = self, keys = colnames)
            return self.StructuredDictSignal
        else:
            return self.StructuredDictSignal
    def deleteSignal(self):
        print 'Delete Signal'
        if self.workingDataFrameData:
            self.R('if(exists("' + self.workingDataFrameData.getData() + '")) { rm(' + self.workingDataFrameData.getData() + ') }')
        
        self.workingDataFrameData = None
    def _convertToList(self):
        #self.R('list_of_'+self.workingData+'<-as.list('+self.workingData+')')
        if self.workingDataFrameData:
            return self.workingDataFrameData._convertToList()
        else:
            dfData = self._convertToDataFrame()
            
            newData = RList(self.parent, data = 'as.list('+dfData.data+')')
            return newData
    def _convertToDataFrame(self):
        if self.workingDataFrameData:  ## don't reprocess if there is already a valid conversion.
            return self.workingDataFrameData
        ## we need to check if the database is available if not then we can't make the conversion.
        database = self.getDatabase()
        if not os.path.exists(database):
            if self.R('exists('+self.newDataName+')') == 'TRUE':
                QMessageBox.information(self, 'SQLite Conversion','Database '+unicode(database)+' does not exist on your system.\nHowever, I found the data in the Red-R session and will send this\ninstead of making a new table.',  QMessageBox.Ok + QMessageBox.Default)
                newData = RDataFrame(self.widget, data = self.newDataName)
                return newData
            else:
                QMessageBox.information(self, 'SQLite Conversion','Database '+unicode(database)+' does not exist on your system.\nIf you got this schema from someone else this is normal.\nI\'ll make the connection but no data will be sent.',  QMessageBox.Ok + QMessageBox.Default)
                return
        database = database.replace('\\','/')
        if not self.require_librarys(['RSQLite']): # require the sqlite package
            QMessageBox.information(self, 'SQLite Conversion','Can\'t load the RSQLite package.  Please connect to the internet do download.',  QMessageBox.Ok + QMessageBox.Default)
            return
        ## convert the data table to a database.  This will actually run the current sql query which will likely inherit from some kind of view
        if not self.R('exists("%s")' % self.newDataName):
            conn = sqlite3.connect(database)
            cursor = conn.cursor()
            
            cursor.execute('PRAGMA table_info('+self.getData()+')')
            types = []
            for row in cursor:
                types.append((row[1], row[2]))
            conn.close()
            
            ### list all of the column names and allow the user to specify one as the rowname column if desired.
            colnames = [type[0] for type in types]
            colnames.insert(0, 'No Names')
            dialog = redRGUI.base.dialog()
            rownameList = redRGUI.base.listBox(dialog, label = 'Rownames', toolTip = 'Select a column to represent Row Names', callback = dialog.accept)
            rownameList.update(colnames)
            r = dialog.exec_() ## execute the dialog
            if r == QDialog.Accepted and unicode(rownameList.selectedIds()[0]) != 'No Names':
                
                rownameSelection = '\''+unicode(rownameList.selectedIds()[0])+'\''
            else:
                rownameSelection = 'NULL'
            self.R('m<-dbDriver("SQLite")')
            print database
            self.R('con<-dbConnect(m, dbname=\''+database+'\')')
            
            
            self.R(self.newDataName+'<-dbGetQuery(con, statement=\'select * from '+self.getData()+'\', row.names = '+rownameSelection+')')
            self.R('dbDisconnect(con)')  # close the connection
            # it would be really nice if in this we could ask the user to pick the column for the column name
            
            
            # convert the classes of the columns to something reasonable like numeric or factor
            
            for type in types:
                if type[1] in ['text']:
                    self.R(self.newDataName+'$'+unicode(type[0])+'<-as.factor('+self.newDataName+'$'+unicode(type[0])+')')
                elif type[1] in ['real']:
                    self.R(self.newDataName+'$'+unicode(type[0])+'<-as.numeric('+self.newDataName+'$'+unicode(type[0])+')')
            
            if rownameSelection != 'NULL':
                self.R('rownames('+self.newDataName+')<-'+self.newDataName+'$'+rownameSelection.strip('\''))
                self.R(self.newDataName+'<-'+self.newDataName+'[,colnames('+self.newDataName+') != '+rownameSelection+',drop = F]')
            ## allow the user to set rownames???
        
        
        self.workingDataFrameData = RDataFrame(self.parent, data = self.newDataName)
        return self.workingDataFrameData
        
    def getTableModel(self, widget, filterable = True, sortable = True):
        
        #raise Exception("This is a dummy class, no table model can be returned from this class.  The developer must impliment a table model for the requested signal class.")
        return SQLiteTableModel(widget, database = self.getDatabase(), dataTable = self.getData(), filterable = filterable, sortable = sortable)
        
    def execute(self, q):
        c = self.getCursor()
        r = c.execute(q).fetchall()
        self.closeCursor(c)
        return r
        
    def getCursor(self):
        return sqlite3.connect(self.getDatabase()).cursor()
    def closeCursor(self, c):
        c.close()
        
        
class SQLiteTableModel(QAbstractTableModel): 
    def __init__(self, parent, database, dataTable, filterable=False, sortable=True, reloading = False, workingData = None, filteredOn = [], criteriaList = {}, **kwargs): 
        QAbstractTableModel.__init__(self,parent) 
        print 'initiating table'
        self.working = False
        self.range = 500
        self.parent =  parent
        self.sortable = sortable
        self.sortTerm = ''
        self.filterable = filterable
        self.filteredOn = filteredOn
        self.criteriaList = criteriaList
        # self.filter_delete = os.path.join(redREnviron.directoryNames['picsDir'],'filterAdd.png')
        self.columnFiltered = QIcon(os.path.join(redREnviron.directoryNames['picsDir'],'columnFilter.png'))
        
        # print self.filter_add,os.path.exists(self.filter_add),os.path.exists(self.filter_delete)
        self.orgData = dataTable
        self.workingDatabase = database
        self.workingData = self.orgData
        if reloading:
            self.initData(workingData, firstLoad = True)
            
        else:
            self.initData(dataTable, firstLoad = True)
    def getFilteredData(self):
        return self.workingData
    def getSettings(self):
        r = {}
        r['database'] = self.workingDatabase
        r['dataTable'] = self.orgData
        r['filterable'] = self.filterable
        r['sortable'] = self.sortable
        r['reloading'] = True
        r['filteredOn'] = self.filteredOn
        r['criteriaList'] = self.criteriaList
        r['workingData'] = self.workingData
        return r
    def initData(self, dataTable, firstLoad = False):
        c = self.getCursor()
        self.workingData = dataTable
        d = {'DATA':self.workingData}#, 'START':self.currentRange['rstart'], 'END':self.currentRange['rend']}
        print d
        if firstLoad:
            try:
                c.execute('SELECT * FROM (%(DATA)s) LIMIT 1' % d)
                self.colnames = [n[0] for n in c.description]
                self.ncol = len(c.fetchall()[0])
            except:
                self.colnames = ['No valid colnames, please resend connection']
                self.ncol = 1
            
        #print self.nrow
        self.nrow = c.execute("SELECT Count(*) from (%(DATA)s)" % d).fetchall()[0][0]
        #print self.ncol
        self.currentRange = self.getRange(0,0)
        d['START'] = self.currentRange['rstart']
        d['END'] = self.currentRange['rend']
        self.arraydata = c.execute('SELECT * FROM (%(DATA)s) LIMIT %(START)s, %(END)s' % d).fetchall()
        
        # print _('self.arraydata loaded')

        

        if self.arraydata == [[]]:
            toAppend= ['' for i in xrange(self.columnCount(self))]
            self.arraydata = [toAppend]
        self.parent.setModel(self)
        self.closeCursor(c)
        
    def getRange(self,row,col):
        r = {}
        if row-self.range < 0:
            r['rstart'] = 0
        else:
            r['rstart'] = row-self.range
        if row+self.range > self.nrow:
            r['rend'] = self.nrow
        else:
            r['rend'] = row+self.range
        return r
    ##########  functions accessed by filter table  #########
    def getSummary(self):
        c = self.getCursor()
        d = {'DATA': self.workingData, 'ORGDATA': self.orgData}
        total = c.execute("SELECT Count(*) from %(ORGDATA)s" % d).fetchall()[0][0]
        filtered = c.execute("SELECT Count(*) from %(DATA)s" % d).fetchall()[0][0]
        self.closeCursor(c)
        return 'Displaying %d of %s rows' % (filtered, total) 
        
    def startProgressBar(self, title,text,max):
        progressBar = QProgressDialog()
        progressBar.setCancelButtonText(QString())
        progressBar.setWindowTitle(title)
        progressBar.setLabelText(text)
        progressBar.setMaximum(max)
        progressBar.setValue(0)
        progressBar.show()
        return progressBar
        
    def sort(self,col,order):
        #self.tm.sort(col-1,order)
        self.parent.sortByColumn(col-1, order)
        self.parent.horizontalHeader().setSortIndicator(col-1,order)
        self.menu.hide()
        self.parent.sortIndex = [col-1,order]
        
    def createMenu(self, selectedCol):
        """Creates the menu for selection, note that the selectedCol is indexed beginning with 1, which is more common in R but unusual in Python."""
        globalPos = QCursor.pos() #self.mapToGlobal(pos)
        self.menu = QDialog(None,Qt.Popup)
        self.menu.setLayout(QVBoxLayout())

        if self.sortable:
            print 'setting the menu to be sortable.'
            box = widgetBox(self.menu,orientation='horizontal')
            box.layout().setAlignment(Qt.AlignLeft)
            button(box,label='A->Z',callback= lambda: self.sort(selectedCol,Qt.AscendingOrder))
            widgetLabel(box,label=_('Ascending Sort'))
            box = widgetBox(self.menu,orientation='horizontal')
            box.layout().setAlignment(Qt.AlignLeft)
            button(box,label='Z->A',callback= lambda: self.sort(selectedCol,Qt.DescendingOrder))
            widgetLabel(box,label=_('Descending Sort'))
            
        if not self.filterable:
            print 'setting the menu filterable.'
            self.menu.move(globalPos)
            self.menu.show()
            return
        
        if self.sortable:
            hr = QFrame(self.menu)
            hr.setFrameStyle( QFrame.Sunken + QFrame.HLine );
            hr.setFixedHeight( 12 );
            self.menu.layout().addWidget(hr)
    
        
        clearButton = button(self.menu,label=_('Clear Filter'),
        callback=lambda col=selectedCol: self.createCriteriaList(col,self.menu,action='clear'))
        self.menu.layout().setAlignment(clearButton,Qt.AlignHCenter)
        clearButton.hide()
        
        if selectedCol in self.criteriaList.keys():
            clearButton.show()
        
        self.optionsBox = widgetBox(self.menu)
        self.optionsBox.layout().setAlignment(Qt.AlignTop)
        self.whereClause = lineEdit(self.optionsBox, label = 'WHERE %s:' % self.colnames[selectedCol - 1])
        buttonBox = widgetBox(self.optionsBox,orientation='horizontal')
        buttonBox.layout().setAlignment(Qt.AlignRight)
        okButton = button(buttonBox,label=_('OK'),
        callback=lambda col=selectedCol: self.createCriteriaList(col,self.optionsBox,action=_('OK')))
        okButton.setDefault (True)
        button(buttonBox,label=_('Cancel'),
        callback=lambda col=selectedCol: self.createCriteriaList(col,self.optionsBox,action='cancel'))
        
        self.menu.move(globalPos)
        self.menu.show()
    #def factorCheckBox(self,val,menu):
        #if val != 0: return
        #checkbox = menu.findChildren(checkBox)[0]
        #if checkbox.buttonAt(0) != _('Check All'): return
        ##print checkbox.getChecked(), _('Check All') in checkbox.getChecked()
        #if _('Check All') in checkbox.getChecked():
            #checkbox.checkAll()
        #else: 
            #checkbox.uncheckAll()
        
    def clearOthers(self,val, menu, field):
        # print '##############', val, field
        for label,value in zip(menu.findChildren(QLabel),menu.findChildren(QLineEdit)):
            if label.text() != field:
                value.setText('')

    def clearFiltering(self):
        self.criteriaList = {}
        self.workingData = self.orgData
        # self.horizontalHeader().setSortIndicator(-1,order)
        self.filter()
        
    def createCriteriaList(self,col,menu,action):
        """Creates the criteria list.  Note that col is indexed to 1, action is the returned action and menu is the menu dialog object."""
        if action=='cancel':
            self.menu.hide()
            return
        if action =='clear' or self.whereClause.text() == '':
            del self.criteriaList[col-1]
        elif action=='OK':
            self.criteriaList[col-1] = '%s %s' % (self.colnames[col-1], self.whereClause.text())
            
        self.menu.hide()
        self.filter()
    
    def filter(self):
        filters  = self.criteriaList.values()
        if len(filters) > 0:
            filteredData = 'SELECT * FROM (%s) WHERE %s' % (self.orgData,' AND '.join(filters))
        else:
            filteredData = self.orgData
        self.emit(SIGNAL("layoutAboutToBeChanged()"))
        self.initData(filteredData)
        self.emit(SIGNAL("layoutChanged()"))
        if self.parent.onFilterCallback:
            self.parent.onFilterCallback()
    
    def flags(self,index):
        return (Qt.ItemIsSelectable | Qt.ItemIsEnabled)
 
    def rowCount(self, parent): 
        return self.nrow
        #return len(self.arraydata)
    def columnCount(self, parent): 
        return self.ncol
        #return len(self.arraydata[0])
 
    def data(self, index, role): 
        if not index.isValid(): 
            #print 'returning QVariant because data is not valid'
            return QVariant() 
        elif role != Qt.DisplayRole:
            #print 'returning QVariant because this in not a display role'
            return QVariant() 
        elif not self.workingData or self.workingData == None:
            #print 'returning QVariant because there is no data'
            return QVariant()
        if ((self.currentRange['rstart'] + 100 > index.row() and self.currentRange['rstart'] !=0) or 
            (self.currentRange['rend'] - 100 < index.row() and self.currentRange['rend'] != self.nrow)
        ):
            #print self.currentRange, index.row()
            self.currentRange = self.getRange(index.row(), index.column())
            if not self.working:
                print 'Recalculating array', self.currentRange
                self.working = True
                c = self.getCursor()
                d = {'DATA':self.workingData, 'START':self.currentRange['rstart'], 'END':self.currentRange['rend'], 'ORDER':self.sortTerm}
                self.arraydata = c.execute('SELECT * FROM (%(DATA)s) %(ORDER)s LIMIT %(START)s, %(END)s' % d).fetchall()
                self.closeCursor(c)
                self.working = False
                
            else: self.arraydata = []
        if len(self.arraydata) == 0 or len(self.arraydata[0]) == 0:
            #print 'returning QVariant because the data is an empty list'
            return QVariant()
        
        rowInd = index.row() - self.currentRange['rstart']
        colInd = index.column()
        # self.working = False
        #print self.arraydata[rowInd][colInd], rowInd, colInd
        return QVariant(unicode(self.arraydata[rowInd][colInd])) 

    def headerData(self, col, orientation, role):
        if orientation == Qt.Horizontal and role == Qt.DisplayRole:
            if not col >= len(self.colnames):
                return QVariant(self.colnames[col])
        elif orientation == Qt.Horizontal and role == Qt.DecorationRole and (self.filterable or self.sortable):
            if col in self.criteriaList.keys():
                return QVariant(self.columnFiltered)
            else:
                return QVariant()
        elif orientation == Qt.Vertical and role == Qt.DisplayRole: 
            # print 'row number', col, len(self.rownames)
            return QVariant(col)
        return QVariant()
    

    def sort(self, Ncol, order):
        if self.editable: return
        self.emit(SIGNAL("layoutAboutToBeChanged()"))
        #print 'adfasfasdfasdfas', self.R('class(%s)' % self.orgRdata)
        if order == Qt.DescendingOrder:
            self.sortingTerm = 'ORDER BY %s DESC'
        else:
            self.sortingTerm = 'ORDER BY %s ASC'
        
        c = self.getCursor()
        d = {'DATA':self.workingData, 'START':self.currentRange['rstart'], 'END':self.currentRange['rend'], 'ORDER':self.sortTerm}
        self.arraydata = c.execute('SELECT * FROM (%(DATA)s) %(ORDER)s LIMIT %(START)s, %(END)s' % d).fetchall()
        self.closeCursor(c)

        self.emit(SIGNAL("layoutChanged()"))
    ########### SQLite Functions #############
    def getCursor(self):
        return sqlite3.connect(self.workingDatabase).cursor()
    def closeCursor(self, c):
        c.close()
        
        
    